﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MKAdmin.DataHelper
{
    public class DBHelper : IDisposable
    {
        private SqlConnection SqlConnection;
        private SqlTransaction SqlTransaction;

        /// <summary>
        /// 自动释放数据库资源
        /// </summary>
        public void Dispose()
        {
            if (null != SqlConnection)
            {
                SqlConnection.Close();
                SqlConnection.Dispose();
            }
        }

        /// <summary>
        /// 构造函数
        /// </summary>
        public DBHelper(string connStr = "MKAdminConnStr")
        {
            var connectionString = ConfigurationManager.ConnectionStrings[connStr].ConnectionString;

            SqlConnection = new SqlConnection(connectionString);

            SqlConnection.Open();
        }

        /// <summary>
        /// 开启事务
        /// </summary>
        public void BeginTransaction()
        {
            SqlTransaction = SqlConnection.BeginTransaction();
        }

        /// <summary>
        /// 提交事务
        /// </summary>
        public void CommitTransaction()
        {
            if (SqlTransaction != null)
            {
                SqlTransaction.Commit();
                SqlTransaction = null;
            }
        }

        /// <summary>
        /// 回滚事务
        /// </summary>
        public void RollbackTransaction()
        {
            if (SqlTransaction != null)
            {
                SqlTransaction.Rollback();
                SqlTransaction = null;
            }
        }


        /// <summary>
        /// 执行查询，并以DataTable返回结果集
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="commandType"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public DataTable GetDataTable(string sql, CommandType commandType = CommandType.Text, SqlParameter[] parameters = null)
        {
            return GetDataSet(sql, commandType, parameters).Tables[0];
        }

        /// <summary>
        /// 执行查询，并以DataSet返回结果集
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="commandType"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public DataSet GetDataSet(string sql, CommandType commandType = CommandType.Text, SqlParameter[] parameters = null)
        {
            var dataSet = new DataSet();

            using (SqlCommand cmd = new SqlCommand(sql, SqlConnection))
            {
                if (SqlTransaction != null)
                    cmd.Transaction = SqlTransaction;

                cmd.CommandType = commandType;
                if (parameters != null)
                    foreach (SqlParameter parameter in parameters)
                        cmd.Parameters.Add(parameter);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(dataSet);
            }

            return dataSet;
        }

        /// <summary>
        /// 执行增删改成功条数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="commandType"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public int ExecuteQuery(string sql, CommandType commandType = CommandType.Text, SqlParameter[] parameters = null)
        {
            var count = 0;
            using (SqlCommand cmd = new SqlCommand(sql, SqlConnection))
            {
                if (SqlTransaction != null)
                    cmd.Transaction = SqlTransaction;

                cmd.CommandType = commandType;
                if (parameters != null)
                    foreach (SqlParameter parameter in parameters)
                        cmd.Parameters.Add(parameter);
                count = cmd.ExecuteNonQuery();
            }
            return count;
        }

        /// <summary>
        /// 返回首行首列
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="commandType"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public object ExecuteScalar(string sql, CommandType commandType = CommandType.Text, SqlParameter[] parameters = null)
        {
            object result = 0;
            using (SqlCommand cmd = new SqlCommand(sql, SqlConnection))
            {
                if (SqlTransaction != null)
                    cmd.Transaction = SqlTransaction;
                cmd.CommandType = commandType;
                if (parameters != null)
                {
                    cmd.Parameters.AddRange(parameters);
                }
                result = cmd.ExecuteScalar();
            }
            return result;
        }


        /// <summary>
        /// 批量新增
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="tableName"></param>
        public void BulkInserts(DataTable dt, string tableName)
        {
            SqlBulkCopy bulkCopy = new SqlBulkCopy(new DBHelper().SqlConnection)
            {
                DestinationTableName = tableName,
                BatchSize = dt.Rows.Count,

            };
            //映射table和数据库表字段
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                bulkCopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
            }

            try
            {
                if (dt != null && dt.Rows.Count != 0)
                    bulkCopy.WriteToServer(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                new DBHelper().Dispose();
                if (bulkCopy != null)
                    bulkCopy.Close();

            }
        }
    }
}
